package com.scalar.chiptrack.sales.parser;

import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.ScalarException;

import java.io.File;
import java.io.IOException;
import java.io.BufferedReader;
import java.io.FileReader;
import java.util.ArrayList;
import java.util.HashMap;

import jxl.read.biff.BiffException;
import jxl.*;

/**
 * User: Sujan
 * Date: Jan 10, 2007
 * Time: 4:24:04 PM
 *
 * Modified on 21st May 2007 for ANALOGIX
 */
public class SalesParser
{
    private Logger m_parserLogger   = null;

    public SalesParser() throws Exception
    {
        try
        {
            m_parserLogger = new Logger(  "./logs", "backlogPorting", "Parser", "debug", 1024 * 1000, 1 );
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
    }
    // To be implemented by sub classes
    public void execute() throws Exception
    {
        boolean success = false;
        printHeader();
        File backlogFile = getFileForPorting(System.getProperty ( "salesOrderFile" ));
        log ( "Parsing File:"  + backlogFile.getName() );
        System.out.println ( "Parsing Sales Order File:"  + backlogFile.getName());
        /*log ( "Parsing customer sheet...." );
        System.out.println ( "Parsing customer sheet...." );

        HashMap customerMap = parseCustomerFile(backlogFile);

        log ( "Parsing customer compeleted..." );
        System.out.println ( "Parsing customer compeleted..." );*/
        log ( "Parsing Sales Order sheet...." );
        System.out.println ( "Parsing Sales Order sheet...." );

        ArrayList lineItemList = parseSalesOrdersCSVFile( backlogFile ); //parseSalesFile ( backlogFile );

        log ( "Parsing Sales backlog completed..." );
        System.out.println ( "Parsing Sales backlog completed..." );
        log ( "Parsing completed successfully..." );
        System.out.println ( "Parsing completed successfully.....");
        log ( "Porting Sales Backlog info..." );
        System.out.println ( "Porting Sales Backlog info..." );

        DBTransaction dbTransaction = null;
        String portingStatusMsg = null;
        try
        {
            dbTransaction = DBTransaction.getInstance();
            dbTransaction.begin();
            SalesParserDAO.saveSalesOrders( dbTransaction, lineItemList );
            /*int lineItemsSize = (lineItemList != null) ? lineItemList.size() : 0;
            for ( int i=0; i < lineItemsSize; i++ )
            {
                SalesParserLineItem lineItem = (SalesParserLineItem)lineItemList.get(i);
                String customer = lineItem.getCustomerName();
                String endCustomer = lineItem.getEndCustomer();
                String endUser = lineItem.getEndUser();
                if ( customerMap.containsKey(customer) )
                {
                    CustomerInfo customerInfo = (CustomerInfo)customerMap.get(customer);
                    createUpdateCustomer( dbTransaction, customerInfo );
                }
                else
                {
                    log ( "**LineItem CUSTOMER NOT FOUND IN THE MAP: " + customer );
                }
                if ( customerMap.containsKey(endCustomer) )
                {
                    CustomerInfo customerInfo = (CustomerInfo)customerMap.get(endCustomer);
                    createUpdateCustomer( dbTransaction, customerInfo );
                }
                else
                {
                    log ( "**LineItem END CUSTOMER NOT FOUND IN THE MAP: " + endCustomer );
                }
                if ( customerMap.containsKey(endUser) )
                {
                    CustomerInfo customerInfo = (CustomerInfo)customerMap.get(endUser);
                    createUpdateCustomer( dbTransaction, customerInfo );
                }
                *//*else
                {
                    log ( "**END USER NOT FOUND IN THE MAP: " + endUser );
                }*//*

                if ( lineItem.getSalesOrderNo() == null || lineItem.getSalesOrderNo().trim().length() == 0 )
                {
                    String salesOrderNo;
                    if ( lineItem.getUnitPrice() > 0 )
                    {
                        salesOrderNo = SalesOrderDAO.loadAutomatedSalesOrderNo();
                    }
                    else
                    {
                        salesOrderNo = SalesOrderDAO.loadAutomatedSampleSalesOrderNo();
                    }
                    lineItem.setSalesOrderNo( salesOrderNo );
                }
                SalesParserDAO.saveSalesOrderLineItem( dbTransaction, lineItem );
            }*/

            if ( dbTransaction != null )
            {
                dbTransaction.commit();
            }
            success = true;
            int listSize = lineItemList.size();
            for ( int i=0; i<listSize; i++ )
            {
                SalesParserLineItem lineItem = (SalesParserLineItem)lineItemList.get(i);
                switch( lineItem.getPortingStatusCode() )
                {
                    case 0: //'SUCCESS' NEW LINE ITEM CREATED
                        break;
                    case 1: //'SUCCESS' LINE ITEM UPDATED
                        break;
                    case 2: //'FAILED' SALES ORDER NUMBER REQUIRED
                        log ( "***NOT PORTED*** SALES ORDER NUMBER REQUIRED. SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo() );
                        break;
                    case 3: //'FAILED' NO FG PART NUMBER FOUND
                        log ( "***NOT PORTED*** MKTG PART NUMBER NOT FOUND. MKTG PART NUMBER: " + lineItem.getMarketingPartNo() +" SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo() );
                        break;
                    case 4: //'FAILED' CUSTOMER NOT FOUND
                        log ( "***NOT PORTED*** CUSTOMER NOT FOUND. CUSTOMER NUMBER: " + lineItem.getCustomerNumber() +" SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo() );
                        break;
                    case 5: //'FAILED' SHIPPING ADDRESS NOT FOUND
                        log ( "***NOT PORTED*** SHIPPING ADDRESS NOT FOUND. SHIPPING LOCATION: " + lineItem.getShippingLocation() +" SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo() );
                        break;
                }
            }

        }
        catch ( ScalarException e )
        {
            log ( "Exception while porting Sales Backlog Orders:" + e.getRootCause() );
            throw e;
        }
        finally
        {
            if ( dbTransaction != null )
            {
                if ( !success )
                {
                    try { dbTransaction.rollback(); } catch ( DBXException ignore ){}
                }
                dbTransaction = null;
            }
        }
        if ( success ) //SEND PORTING STATUS MAIL
        {
            System.out.println ( "********SEE LOG FILE FOR NOT PORTED LINE ITEMS********" );
            log ( "Porting Sales Backlog completed successfully...." );
            System.out.println ( "Porting Sales Backlog completed successfully...." );

        }
        else
        {
            log ( "Unable to port the customer information...." );
            System.out.println ( "Unable to port the customer information..." );
        }
    }
    private void printHeader()
    {
        String info = "\n\n+----------- Running Sales Backlog Parser on " + new java.util.Date( System.currentTimeMillis() ) + " -------------+\n" +
                 "\n+---------------------------------------------------------------+\n" +
                 "+                                                                \n" +
                 "+            Sales Backlog Parser - Beta          \n" +
                 "+                                                                \n" +
                 "+    Copy Right (C) 2007 - ScalarSoft Inc., All rights reserved.  \n" +
                 "+                                                                \n" +
                 "+---------------------------------------------------------------+\n" ;
        System.out.println( info );
        log( info );
    }
    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + AdaptorConstants.LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    }

   private File getFileForPorting ( String salesPortingFile ) throws ScalarException
    {
        if ( salesPortingFile == null || salesPortingFile.trim().length() == 0 )
        {
            log ( "Sales Porting File Name not specified" );
            System.out.println ( "Sales Porting File Name not specified" );
            throw new ScalarException( "Sales Porting File Name not specified" );
        }
        File portingFile = new File ( salesPortingFile );
        if ( portingFile.exists() == false && portingFile.isFile() == false )
        {
            log ( "Sales Porting File:" + salesPortingFile + " Does not exist" );
            throw new ScalarException( salesPortingFile + " does not exist" );
        }
        else
        {
            log ( "Sales Porting File:" + salesPortingFile + " Found" );
            System.out.println ( "Sales Porting File:" + salesPortingFile + " Found" );
        }
        return portingFile;
    }
    private ArrayList parseSalesFile ( File salesPortingFile ) throws IOException, BiffException
    {
        Workbook workbook 	        = Workbook.getWorkbook( salesPortingFile );
        Sheet sheet 		        = workbook.getSheet( "All_Records" );
        Cell[] cells 			    = null;
        SalesParserLineItem lineItem = null;
        ArrayList lineItemList      = new ArrayList();
        int noOfCells               = 0;
        int noOfRows                = sheet.getRows();
        for ( int rowCount = 1; rowCount < noOfRows; rowCount++ )
        {
            cells = sheet.getRow( rowCount );
            noOfCells = cells.length;
            if ( noOfCells > 0  )
            {
                if ( cells[0].getContents() != null && cells[0].getContents().trim().length() > 0 && !"#N/A".equalsIgnoreCase(cells[0].getContents().trim()) ) //Customer available
                {
                    lineItem = new SalesParserLineItem();
                    lineItem.setCustomerName(cells[0].getContents().trim());
                }
                else
                {
                    log( "**NOT PORTED**Customer Name Missing for the row:" + (rowCount+1) );
                    continue;
                }


                if ( noOfCells > 1 && cells[1].getContents() != null && !"#N/A".equalsIgnoreCase(cells[1].getContents().trim()) ) //End Customer
                {
                    lineItem.setEndCustomer(cells[1].getContents().trim());
                }
                /*else
                {
                    log( "**NOT PORTED**Shipping Location Missing for the row:" + (rowCount+1) );
                    continue;
                }*/
                if ( noOfCells > 2 && cells[2].getContents() != null ) //END USER..
                {
                    lineItem.setEndUser(cells[2].getContents().trim());
                }
                if ( noOfCells > 3 && cells[3].getContents() != null ) //Sales Person
                {
                    lineItem.setSalesPerson(cells[3].getContents().trim().startsWith("-") ? null : cells[3].getContents().trim() );
                }
                if ( noOfCells > 4 && cells[4].getContents() != null ) //PO NUMBER
                {
                    lineItem.setCustomerPO(cells[4].getContents().trim());
                }
                if ( noOfCells > 5 && cells[5].getContents() != null && cells[5].getContents().trim().length() > 0 ) //SO NUMBER
                {
                    lineItem.setSalesOrderNo(cells[5].getContents().trim());
                }
                /*else
                {
                    log ( "**NOT PORTED**Sales Order Number MISSING for the row:" + (rowCount+1) );
                    continue;
                }*/
                if ( noOfCells > 6 && cells[6].getContents() != null ) //Line Item Number
                {
                    int lineItemNo = 1;
                    try { lineItemNo = Integer.parseInt (cells[6].getContents().trim()); } catch (NumberFormatException ignore){}
                    lineItem.setLineItemNo( lineItemNo );
                }
                if ( noOfCells > 7 && cells[7].getContents() != null ) //Part Number
                {
                    lineItem.setManufacturingPartNo(cells[7].getContents().trim());
                }
                if ( noOfCells > 8 && cells[8].getContents() != null ) //Qty
                {
                    int qty = 0;
                    try { qty = Integer.parseInt ( cells[8].getContents().trim()); } catch ( NumberFormatException ignore ) {}
                    lineItem.setQuantity( qty );
                }
                if ( noOfCells > 9 && cells[9].getContents() != null ) //PRICE
                {
                    float price = 0f;
                    try { price = Float.parseFloat( cells[9].getContents().trim() ); } catch ( NumberFormatException ignore ) {}
                    lineItem.setUnitPrice( price );
                    if ( price == 0 )
                    {
                        lineItem.setIsSample(0);
                    }
                    else
                    {
                        lineItem.setIsSample(1);
                    }
                }
                if ( noOfCells > 10 && cells[10].getContents() != null ) //Total Amt
                {
                    double totalAmt = 0d;
                    try { totalAmt = Double.parseDouble( cells[10].getContents().trim() ); } catch ( NumberFormatException ignore ) {}
                    lineItem.setTotalAmt( totalAmt );
                }
                if ( noOfCells > 11 && cells[11].getContents() != null ) //Req. Dock Date
                {
                    String dt = cells[11].getContents().trim().length() > 0 ? cells[11].getContents().trim() : null;
                    lineItem.setRequestedDockDate( StringUtils.formatDate( dt, "MM/dd/yy", "MM/dd/yyyy") );
                }
                if ( noOfCells > 12 && cells[12].getContents() != null ) //Sch. Date
                {
                    String dt = cells[12].getContents().trim().length() > 0 ? cells[12].getContents().trim() : null;
                    lineItem.setScheduleDate(StringUtils.formatDate( dt, "MM/dd/yy", "MM/dd/yyyy"));
                }
                if ( noOfCells > 13 && cells[13].getContents() != null ) //Revised Sch. Date
                {
                    String dt = cells[13].getContents().trim().length() > 0 ? cells[13].getContents().trim() : null;
                    lineItem.setRevisedScheduleDate( StringUtils.formatDate( dt, "MM/dd/yy", "MM/dd/yyyy"));
                }
                if ( noOfCells > 14 && cells[14].getContents() != null ) //Comments
                {
                    lineItem.setDescription(cells[14].getContents().trim());
                }
                if ( noOfCells > 15 && cells[15].getContents() != null ) //Shipped Date
                {
                    String dt = cells[15].getContents().trim().length() > 0 ? cells[15].getContents().trim() : null;
                    lineItem.setShippedDate( StringUtils.formatDate( dt, "MM/dd/yy", "MM/dd/yyyy") );
                }
                if ( noOfCells > 17 && cells[17].getContents() != null ) //Booking Date
                {
                    String dt = cells[17].getContents().trim().length() > 0 ? cells[17].getContents().trim() : null;
                    //dt = dt.substring( 0, dt.lastIndexOf("/") + 3 );
                    //if( dt != null && ( dt.trim().length() == 7 || dt.trim().length() == 8 ) )
                    //{
                        lineItem.setBookingDate( StringUtils.formatDate( dt, "MM/dd/yy hh:mm", "MM/dd/yyyy") );
                    //}
                    if( rowCount < 15  )
                    {
                        System.out.println("SalesParser.parseSalesFile -  dt = " + dt);
                        System.out.println("SalesParser.parseSalesFile -  lineItem.getBookingDate() = " + lineItem.getBookingDate() );
                    }
                }
            }
            lineItemList.add(lineItem);
        }
        return lineItemList;
    }
    public static void main ( String[] args ) throws Exception
    {
        SalesParser salesParser = new SalesParser();
        salesParser.execute();
    }

    private String getFormattedDate ( String date )
    {
        //date: 61221 ==> 6 -> 2006, 12 -> month, 21 -> day
        //formattedDate: mm/dd/yyyy ==> 12/21/2006

        String formattedDate = "";
        if ( date != null && date.length() == 5 )
        {
            String year = "200" + date.substring(0,1);
            String month = date.substring(1,3);
            String day = date.substring(3,5);
            formattedDate = month + "/" + day + "/" + year;
        }
        return formattedDate;
    }
    private HashMap parseCustomerFile ( File customerPortingFile ) throws IOException, BiffException
    {
        Workbook workbook 	        = Workbook.getWorkbook( customerPortingFile );
        Sheet sheet 		        = workbook.getSheet( "Customers" );
        Cell[] cells 			    = null;
        CustomerInfo customerInfo   = null;
        Address billingAddress = null;
        Address shippingAddress = null;
        int noOfCells               = 0;
        int noOfRows                = sheet.getRows();
        HashMap customerMap         = new HashMap();
        for ( int rowCount = 1; rowCount < noOfRows; rowCount++ )
        {
            cells = sheet.getRow( rowCount );
            noOfCells = cells.length;
            if ( noOfCells > 4  )
            {
                if ( cells[4].getContents() != null && cells[4].getContents().trim().length() > 0 ) //Customer Name available
                {
                    customerInfo = (CustomerInfo)customerMap.get(cells[4].getContents().trim());
                    if ( customerInfo == null )
                    {
                        customerInfo = new CustomerInfo(cells[4].getContents().trim(), cells[4].getContents().trim());
                        customerMap.put ( customerInfo.getCustomerNumber(), customerInfo );
                    }
                }
                else
                {
                    log( "Customer Name Missing for the Row:" + (rowCount+1) );
                    continue;
                }
                billingAddress = new Address();
                billingAddress.setAddress1( customerInfo.getCustomerName() );
                shippingAddress = new Address();
                shippingAddress.setDefaultAddress("true");

                if ( noOfCells > 5 ) //billing billingAddress contact person
                {
                    billingAddress.setContactPerson( cells[5].getContents().trim() );
                }

                if ( noOfCells > 6 ) //billing billingAddress2
                {
                    billingAddress.setAddress2( cells[6].getContents().trim() );
                }

                if ( noOfCells > 7 ) //billing city
                {
                    billingAddress.setCity( cells[7].getContents().trim() );
                }
                if ( noOfCells > 8 ) //billing state
                {
                    billingAddress.setState( cells[8].getContents().trim() );
                }
                if ( noOfCells > 9 ) //billing zip
                {
                    billingAddress.setZipCode( cells[9].getContents().trim() );
                }
                if ( noOfCells > 10 ) //billing country
                {
                    billingAddress.setCountry( cells[10].getContents().trim() );
                }

                if ( noOfCells > 11 ) //Shipping addrs1
                {
                    shippingAddress.setAddress1( cells[11].getContents().trim() );
                    if ( !shippingAddress.getAddress1().equalsIgnoreCase( customerInfo.getCustomerName() ) )
                    {
                        CustomerInfo newCustomerInfo = (CustomerInfo)customerMap.get(shippingAddress.getAddress1());
                        if ( newCustomerInfo == null )
                        {
                            newCustomerInfo = new CustomerInfo( shippingAddress.getAddress1(), shippingAddress.getAddress1() );
                            newCustomerInfo.addShippingAddress(shippingAddress);
                            newCustomerInfo.setBillingAddress(new Address());
                            customerMap.put ( newCustomerInfo.getCustomerNumber(), newCustomerInfo );
                        }
                    }
                }
                if ( noOfCells > 12 ) //Shipping addrs Contact Person
                {
                    shippingAddress.setContactPerson( cells[12].getContents().trim() );
                }
                if ( noOfCells > 13 ) //shipping addrs2
                {
                    shippingAddress.setAddress2( cells[13].getContents().trim() );
                }

                if ( noOfCells > 14 ) //shipping city
                {
                    shippingAddress.setCity( cells[14].getContents().trim() );
                }
                if ( noOfCells > 15 ) //shipping state
                {
                    shippingAddress.setState( cells[15].getContents().trim() );
                }
                if ( noOfCells > 16 ) //shipping zip
                {
                    shippingAddress.setZipCode( cells[16].getContents().trim() );
                }
                if ( noOfCells > 17 ) //shipping country
                {
                    shippingAddress.setCountry( cells[17].getContents().trim() );
                }
                if ( noOfCells > 18 ) //freight carrier
                {
                    customerInfo.setShippingMethod( cells[18].getContents().trim() );
                }

                customerInfo.setBillingAddress( billingAddress );
                customerInfo.addShippingAddress(shippingAddress);
            }
            else
            {
                log ( "Could not found Customer Number Cell for the Row:" + (rowCount+1) );
                continue;
            }
        }
        return customerMap;
    }
    private void createUpdateCustomer ( DBTransaction dbTransaction, CustomerInfo customerInfo ) throws ScalarException
    {
        final int defaultBillingAddressStatusCode = 1;
        final int defaultShippingAddressStatusCode = 2;
        int customerId              = -1;

        //int dbCustId = CustomerParserDAO.getCustomerId(dbTransaction,customerInfo.getCustomerNumber());
        customerId = CustomerParserDAO.saveCustomer( dbTransaction, customerInfo.getCustomerName(), customerInfo.getCustomerNumber(), customerInfo.getDeliveryTerms(), Constants.CUSTOMER_TYPE_CUSTOMER, customerInfo.getContactPerson(), customerInfo.isDistributor(), customerInfo.getRegion(), customerInfo.getShippingMethod(), customerInfo.getShippingAccountNumber());
        int addrsStatusCode = 0;
        if ( customerId > 0 )
        {
            ArrayList billingAddresses = new ArrayList();
            billingAddresses.add ( customerInfo.getBillingAddress() );
            addrsStatusCode = CustomerParserDAO.saveCustomerAddresses(dbTransaction, customerId, billingAddresses, "BILLING" );
            if ( (addrsStatusCode & defaultBillingAddressStatusCode) != defaultBillingAddressStatusCode ) //NO DEFAULT BILLING ADDRESS
            {
                log ( "NO Default Billing Address Found For the Customer:" +  customerInfo.getCustomerName() );
            }

            addrsStatusCode = CustomerParserDAO.saveCustomerAddresses(dbTransaction, customerId, customerInfo.getShippingAddresses(), "SHIPPING" );

            if ( (addrsStatusCode & defaultShippingAddressStatusCode) != defaultShippingAddressStatusCode ) //NO DEFAULT SHIPPING ADDRESS
            {
                log ( "NO Default Shipping Address Found For the Customer:" +  customerInfo.getCustomerName() );
            }
        }
        else
        {
            log ( "****ERROR***Could not create Customer:" + customerInfo.getCustomerName() );
            System.out.println ( "****ERROR***Could not create Customer:" + customerInfo.getCustomerName() );
        }
    }
    private ArrayList parseSalesOrdersCSVFile ( File salesPortingFile ) throws IOException
    {
        int rowNo = 0;
        int noOfColumns = 0;
        String[] columns = null;
        String delim = ",";
        SalesParserLineItem lineItem = null;
        ArrayList lineItemList      = new ArrayList();
        BufferedReader br = new BufferedReader( new FileReader( salesPortingFile ) );
        String line = null;
        line = br.readLine();//IGNORING FIRST LINE
        rowNo = 1;
        while ( ( line = br.readLine() ) != null && line.length() > 0 )
        {
            rowNo++;
            //----
            String[] lineToken = line.split("\"");
            if (lineToken != null && lineToken.length > 1)
            {
                for ( int i=1; i < lineToken.length; i=i+2 )
                {
                    lineToken[i] = lineToken[i].replaceAll(",", "@");
                }
            }

            line = "";
            for (int i = 0; i < lineToken.length; i++)
            {
                line = line + lineToken[i];
            }
            //----
            line = line.replaceAll( "\"", "\\\"" );
            columns = line.split(delim);
            noOfColumns = columns.length;
            boolean ignoreLineItem = true;
            Address shipAddress = null;
            for ( int i=0; i < noOfColumns; i++ )
            {
                switch( i )
                {
                    case 0:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Customer available
                        {
                            lineItem = new SalesParserLineItem();
                            lineItem.setCustomerNumber( columns[i].trim() );
                            ignoreLineItem = false;
                        }
                        else
                        {
                            log( "**NOT PORTED** Customer Number Missing for the row:" + (rowNo) );
                            ignoreLineItem = true;
                        }
                        break;
                    case 1:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Sales Order #
                        {
                            lineItem.setSalesOrderNo( columns[i].trim() );
                        }
                        else
                        {
                            log( "**NOT PORTED** SalesOrder Number Missing for the row:" + (rowNo) );
                            ignoreLineItem = true;
                        }
                        break;
                    case 2:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Date
                        {
                            lineItem.setBookingDate( StringUtils.formatDate( columns[i].trim(), "MM/dd/yy", "MM/dd/yyyy") );
                        }
                        else
                        {
                            log( "**NOT PORTED** SalesOrder Booking Date Missing for the row:" + (rowNo) );
                            ignoreLineItem = true;
                        }
                        break;
                    case 3:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship By
                        {
                            lineItem.setRequestedDockDate( StringUtils.formatDate( columns[i].trim(), "MM/dd/yy", "MM/dd/yyyy") );
                        }
                        else
                        {
                            log( "**NOT PORTED** SalesOrder Requested Dock Date Missing for the row:" + (rowNo) );
                            ignoreLineItem = true;
                        }
                        break;
                    case 4:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Closed
                        {
                            lineItem.setShippedDate( "TRUE".equalsIgnoreCase( columns[i].trim() ) ? lineItem.getRequestedDockDate() : null );
                        }                        
                        break;
                    case 5:
                        //IGNORE...
                        break;
                    case 6:
                        //INGORE...
                        break;
                    case 7:
                        shipAddress = new Address();
                        lineItem.setShipAddress( shipAddress );
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship to Name
                        {
                            shipAddress.setAddress1( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 8:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship to Address-Line One
                        {
                            shipAddress.setAddress2( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 9:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship to Address-Line Two
                        {
                            shipAddress.setAddress3( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 10:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship to City
                        {
                            shipAddress.setCity( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 11:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship to State
                        {
                            shipAddress.setState( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 12:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship to Zipcode
                        {
                            shipAddress.setZipCode( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 13:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship to Country
                        {
                            shipAddress.setCountry( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 14:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Customer PO
                        {
                            lineItem.setCustomerPO( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 15:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Ship Via
                        {
                            lineItem.setShippingMethod( columns[i].trim() );
                        }
                        break;
                    case 16:
                        //IGNORE..
                        break;
                    case 17:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Displayed Terms
                        {
                            lineItem.setPaymentTerms( columns[i].trim() );
                        }
                        break;
                    case 27:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //So Distribution
                        {
                            try
                            {
                                lineItem.setLineItemNo( Integer.parseInt( columns[i].trim() ) );
                            }
                            catch ( NumberFormatException ignore )
                            {
                                log( "**NOT PORTED** Line Item Number<"+ columns[i].trim() +"> is not Integer for the row:" + (rowNo) );
                                ignoreLineItem = true;
                            }
                        }
                        break;
                    case 28:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Quantity
                        {
                            try
                            {
                                int qty = (int)Float.parseFloat( columns[i].trim() );
                                if ( qty > 0 )
                                {
                                    lineItem.setQuantity( qty );
                                }
                                else
                                {
                                    log( "**NOT PORTED** Line Item Quantity is ZERO for the row:" + (rowNo) );
                                    ignoreLineItem = true;
                                }
                            }
                            catch ( NumberFormatException ignore )
                            {
                                log( "**NOT PORTED** Line Item Quantity<"+ columns[i].trim() +"> is not Integer for the row:" + (rowNo) );
                                ignoreLineItem = true;
                            }
                        }
                        break;
                    case 30:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Item ID
                        {
                            lineItem.setMarketingPartNo( columns[i].trim() );
                        }
                        else
                        {
                            log( "**NOT PORTED** Marketing Part Number is EMPTY for the row:" + (rowNo) );
                            ignoreLineItem = true;
                        }
                        break;
                    case 33:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Description
                        {
                            lineItem.setDescription( columns[i].trim().replaceAll("@", ",") );
                        }
                        break;
                    case 35:
                        if ( columns[i] != null && columns[i].trim().length() > 0 ) //Quantity
                        {
                            try
                            {
                                float price = Float.parseFloat( columns[i].trim() );
                                lineItem.setUnitPrice( price );
                            }
                            catch ( NumberFormatException ignore )
                            {
                                log( "**NOT PORTED** Line Item Unit Price<"+ columns[i].trim() +"> is not a Number for the row:" + (rowNo) );
                                ignoreLineItem = true;
                            }
                        }
                        break;

                }
                if ( ignoreLineItem )
                {
                    break;
                }
            }
            if ( !ignoreLineItem )
            {
                lineItemList.add ( lineItem );
            }
        }

        try
        {
            br.close();
        }catch (IOException ignore) {}

        return lineItemList;
    }
}
